package cn.antia.cwin.db;

import cn.antia.db.JdbcSql;
import cn.antia.db.SQLGene;
import cn.antia.cwin.Cele;
import cn.antia.util.StringUtil;

import java.util.List;
import java.util.Map;

/**
 * User: Jacob Xiao
 * Date: 2008-7-13
 * Time: 20:18:52
 */
public abstract class AbstractCwinSQLGene implements CwinSQLGene{
    private SQLGene sqlGene;
    public SQLGene getSqlGene() {
        return sqlGene;
    }
    public void setSqlGene(SQLGene sqlGene) {
        this.sqlGene = sqlGene;
    }

    /**
	 * 生成查询语句，条件为ID
	 * */
	public String getSql(String table,int id){
		StringBuffer sb = new StringBuffer("select * from");
		sb.append(table).append(" where id = ").append(id);
		return sb.toString();
	}

    /**
	 * 生成查询语句，条件为键值对
	 * 查询出所有的字段
	 * */
	public JdbcSql findSql(String table, Map<Cele,Object> conditions){
		StringBuffer sb = new StringBuffer("select * from ");
		JdbcSql jdbcSql = this.getConditionSql(conditions);
		sb.append(table).append(jdbcSql.getSql());
		jdbcSql.setSql(sb.toString());
		return jdbcSql;
	}
    /**
	 * 生成SQL语句
	 * @param table		表名
	 * @param fields	查询的字段
	 * @param conditions	查询条件
	 * @return sql语句　(带有点位符的SQL语句)
	 */
	public JdbcSql findSql(String table,List<String> fields,Map<Cele,Object> conditions){
		StringBuffer sb = new StringBuffer("select ");
		if(fields == null || fields.size() < 1){
			sb.append("* ");
		}else{
            boolean isFirst = true;
            for(String f:fields){
				if(!isFirst)sb.append(",");
				sb.append(f);
                isFirst = false;
            }
		}
		JdbcSql sql = this.getConditionSql(conditions);
		sb.append(" from ").append(table).append(sql.getSql());
		sql.setSql(sb.toString());
		return sql;
	}

    /**
	 * 生成插入语句，条件为键值对
	 * elements 中必须包含 id 字段
	 * */
	public JdbcSql insertSql(String table,Map<Cele,Object> elements){
		JdbcSql jdbcSql = new JdbcSql();
        Object[] paraValues = new Object[elements.size()];
        StringBuffer sql = new StringBuffer("insert into ");
		sql.append(table);
		//处理好ID的生成
		StringBuffer fields = new StringBuffer(" (ID");
		StringBuffer values = new StringBuffer(" values(").append(sqlGene.getMaxID(table));
        int i = 0;
        for (Map.Entry<Cele,Object> entry : elements.entrySet()) {
            Cele ec = entry.getKey();
            Object value = entry.getValue();
            fields.append(",").append(ec.getField().getFieldName());
            if (value == null) {
                value = ec.getField().getDefault();
            }
            paraValues[i++] = value;
            values.append(",?");
            /* boolean isInt = false;
            boolean isString = false;
            if (ec.getField().getDbType().toLowerCase().indexOf("char") > -1 || ec.getField().getDbType().toLowerCase().indexOf("text") > -1) {
                isString = true;
            }
            if (value == null) {
                isInt = true;
            }
            if (isInt) {
                values.append(",").append(value);
            } else if (isString) {
                paraValues[i++] = value;
                values.append(",?");
            } else {
                values.append(",'").append(value).append("'");
            }*/
        }
        fields.append(")");
		values.append(")");
		sql.append(fields).append(values);
		jdbcSql.setSql(sql.toString());
		jdbcSql.setValues(paraValues);
		return jdbcSql;
	}

    /**
	 * 生成更新语句，条件为elements
	 *
	 * */
	public JdbcSql updateSql(String table,Map<Cele,Object> elements){
		JdbcSql jdbcSql = new JdbcSql();
		Object[] paraValues = new Object[elements.size()];
		StringBuffer sql = new StringBuffer("update ");
		sql.append(table).append(" set ");
		String key;
		Object value;
		Object id = "";
		int n = 0;
        int i = 0;
        for (Map.Entry<Cele,Object> entry : elements.entrySet()) {
            Cele ec = entry.getKey();
            key = ec.getField().getFieldName();
            value = entry.getValue();
            //如果是ID，则不参与更新，因为ID将作为更新条件
            if (key.equalsIgnoreCase("id")) {
                id = value;
                continue;
            }
            //如果是不是第一个设置的值，则加上“，”号
            if (n == 0) {
                n++;
            } else {
                sql.append(",");
            }
            sql.append(key).append("=?");
            paraValues[i++] = value;
            /*
            boolean isInt = false;
            boolean isString = false;
            if (ec.getField().getDbType().toLowerCase().indexOf("char") > -1 || ec.getField().getDbType().toLowerCase().indexOf("text") > -1) {
                isString = true;
            }
            //如果值为NULL，则当整形数处理
            if (value == null) {
                isInt = true;
            }
            if (isInt) {
                sql.append(key).append("=").append(value).append("");
                //如果是字符形，则让他转义
            } else if (isString) {
                sql.append(key).append("=?");
                paraValues[i++] = value;
            } else {
                sql.append(key).append("='").append(value).append("'");
            }*/
        }
        if(id == null || id.equals("")){
			//错误
            return null;
        }else{
			sql.append(" where id = ").append(id);
		}
        jdbcSql.setSql(sql.toString());
        jdbcSql.setValues(paraValues);
		return jdbcSql;
	}

    /**
 * 生成删除语句，条件为键值对
 * */
	public JdbcSql deleteSql(String table,Map<Cele,Object> elements){
		StringBuffer sb = new StringBuffer("delete from ");
		JdbcSql jdbcSql = this.getConditionSql(elements);
		sb.append(table).append(jdbcSql.getSql());
		jdbcSql.setSql(sb.toString());
		return jdbcSql;
	}

	/**
	 * 生成删除语句，条件为ID
	 * */
	public String deleteSql(String table,int id){
		StringBuffer sql = new StringBuffer("delete from ");
		sql.append(table);
		sql.append(" where id = ");
		sql.append(id);
		return sql.toString();
	}

    public JdbcSql findRecordCount(String table,Map<Cele,Object> conditions) {
		JdbcSql jdbcSql = getConditionSql(conditions);
		String sql;
		if(jdbcSql != null && jdbcSql.getSql() != null){
			sql = new StringBuffer("select count(*) count from ").append(table).append(" ").append(jdbcSql.getSql()).toString();
		}else{
			sql = new StringBuffer("select count(*) count from ").append(table).toString();
		}
        if(jdbcSql != null)
            jdbcSql.setSql(sql);
		return jdbcSql;
	}

    /**
	 * 返回带有占位符的SQL语句
	 * @param table			表名
	 * @param conditions	条件
	 * @param page 	页码
	 * @param pageSize		页大小
	 * @return	sql
	 * @throws cn.antia.cep.db.DBException
	 */
	public JdbcSql findSqlPage(String table,Map<Cele,Object> conditions,String orderField,boolean isAsc, int page, int pageSize){
		int start = (page - 1) * pageSize + 1;
        int end = start + pageSize - 1;
        return findSql(table, conditions, orderField, isAsc, start, end);
	}
    public JdbcSql findSql(String table, Map<Cele, Object> conditions, String orderField, boolean isAsc, int start, int end) {
        StringBuffer sb = new StringBuffer("select * from ");
		JdbcSql jdbcSql = this.getConditionSql(conditions);
		sb.append(table).append(jdbcSql.getSql());
		//分页
        jdbcSql.setSql(sqlGene.composeSql(sb.toString(),orderField,isAsc,start,end));
        return jdbcSql;
    }
    /**
	 * 返回带有占位符的SQL语句(带有指定的要查询的字段)
	 * @param table		表
	 * @param fields	要查询的字段
	 * @param conditions	条件
	 * @param page 	page
	 * @param pageSize		结束记录
	 * @return	sql
	 * @throws cn.antia.cep.db.DBException
	 */
	public JdbcSql findSqlPage(String table, List<String> fields,Map<Cele,Object>conditions,String orderField,boolean isAsc,int page, int pageSize){
		int start = (page - 1) * pageSize + 1;
        int end = start + pageSize - 1;
        return findSql(table,fields, conditions, orderField, isAsc, start, end);
	}
    public JdbcSql findSql(String table, List<String> fields, Map<Cele, Object> conditions, String orderField, boolean isAsc, int start, int end) {
        StringBuffer sb = new StringBuffer("select ");
		if(fields == null || fields.size() < 1){
			sb.append("* ");
		}else {
            sb.append(StringUtil.arrayToString(fields, ","));
        }
		//加入start 与 end
		JdbcSql jdbcSql = this.getConditionSql(conditions);
		if(jdbcSql.getSql() == null || jdbcSql.getSql().trim().length() < 1){
			sb.append(" from ").append(table);
		}else{
			sb.append(" from ").append(table).append(jdbcSql.getSql());
		}
        jdbcSql.setSql(sqlGene.composeSql(sb.toString(),orderField,isAsc,start,end));
		return jdbcSql;
    }
}
